﻿use housing 
go
if exists(select 1 from sysobjects where xtype='P' and name='BuildDataByYearMonth') drop proc BuildDataByYearMonth
go
Create proc BuildDataByYearMonth
(	@YearMonth  int
)
as 
begin 
	insert into apply
	select [apply_noid]
		  ,[apply_name]
		  ,[apply_address]
		  ,[apply_phone]
		  ,[apply_checkstate]
		  ,[apply_time]
		  ,[apply_passed]
		  ,[apply_street]
		  ,[apply_yearincome]
		  ,[apply_peoplenum]
		  ,[apply_avgincome]
		  ,[apply_totlearea]
		  ,[apply_avgarea]
		  ,[apply_totleproperty]
		  ,[apply_paybycard]
		  ,[apply_docname]
		  ,[apply_docnum]
		  ,[apply_docdate]
		  ,[apply_longid]
		  ,[apply_updchecked]
		  ,[apply_familyNum]
		  ,[AddTime]
		  ,[EditTime]
		  ,[ReduceMoney]
		  ,[SubsidyMoney]
		  ,[EditNote]
		  ,[isLengthenAYear]
		  ,[isReduce]
		  ,[isSubsidy]
		  ,[isKind]
		  ,@YearMonth
			,CutSubsidyNum
			,AddSubsidyNum
	  FROM [Apply] where [EffectDate]=@YearMonth-1 --上月
	and (cast([apply_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([apply_noid] as varchar(8))+cast([EffectDate] as varchar(4))) from apply where  [EffectDate]= @YearMonth  )  --已存在不要
	--and [apply_docdate]>=@YearMonth --有效期没过

	-----------------------------------------------------
	insert into [Applicant]
	SELECT 0
		  ,[app_noid]
		  ,[app_title]
		  ,[app_name]
		  ,[app_marryed]
		  ,[app_workunit]
		  ,[app_idcard]
		  ,[app_income]
		  ,[app_ispro]
		  ,@YearMonth
	  FROM [Applicant] where  [EffectDate]=@YearMonth-1 --上月
	and (cast([app_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([app_noid] as varchar(8))+cast([EffectDate] as varchar(4))) 
	from [Applicant] where  [EffectDate]= @YearMonth  )--已存在不要


	--对应
	update [Applicant] set app_applyid=
	(select top 1 apply_id from Apply where Apply.apply_noid=[Applicant].app_noid and  Apply.[EffectDate]=[Applicant].[EffectDate])
	where app_applyid=0
	-----------------------------------------------------
	insert into Housing
	SELECT       
	   0
      ,[hou_noid]
      ,[hou_address]
      ,[hou_area]
      ,[hou_owner]
      ,[hou_ownproof]
      ,[hou_nature]
      ,[hou_remark]
      ,[hou_addcounted]
      ,[applyid]
      ,@YearMonth
  FROM [Housing] where  [EffectDate]=@YearMonth-1 --上月
	and (cast([hou_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([hou_noid] as varchar(8))+cast([EffectDate] as varchar(4))) 
	from [Housing] where  [EffectDate]= @YearMonth  )--已存在不要


	--对应
	update [Housing] set hou_applyid=
	(select top 1 apply_id from Apply where Apply.apply_noid=[Housing].[hou_noid] and  Apply.[EffectDate]=[Housing].[EffectDate])
	where hou_applyid=0
	-----------------------------------------------------
	insert into KindRecord
	SELECT       
	   [adj_longid]
      ,[adj_checked]
      ,[adj_location]
      ,[adj_nowarea]
      ,[adj_nowusearea]
      ,0
      ,[adj_iscontinue]
      ,[adj_starttime]
      ,[adj_stoptime]
      ,[adj_remark]
      ,[adj_date]
      ,[adj_noid]
      ,[adj_reason]
      ,@YearMonth
      ,0
  FROM [KindRecord] where  [EffectDate]=@YearMonth-1 --上月
	and (cast([adj_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([adj_noid] as varchar(8))+cast([EffectDate] as varchar(4))) 
	from [KindRecord] where  [EffectDate]= @YearMonth  )--已存在不要


	--对应
	update [KindRecord] set applyid=
	(select top 1 apply_id from Apply where Apply.apply_noid=[KindRecord].[adj_noid] and  Apply.[EffectDate]=[KindRecord].[EffectDate])
	where applyid=0
-----------------------------------------------------
	insert into Property
	SELECT       
	   0
      ,[pro_noid]
      ,[pro_securitie]
      ,[pro_estate]
      ,[pro_device]
      ,[pro_others]
      ,@YearMonth
  FROM Property where  [EffectDate]=@YearMonth-1 --上月
	and (cast([pro_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([pro_noid] as varchar(8))+cast([EffectDate] as varchar(4))) 
	from Property where  [EffectDate]= @YearMonth  )--已存在不要


	--对应
	update Property set pro_applyid=
	(select top 1 apply_id from Apply where Apply.apply_noid=Property.[pro_noid] and  Apply.[EffectDate]=Property.[EffectDate])
	where pro_applyid=0
	-----------------------------------------------------
	insert into ReduceRecord
	SELECT       
	   [adj_longid]
      ,[adj_checked]
      ,[adj_nowarea]
      ,[adj_nowmoney]
      ,[adj_nowreduce]
      ,0
      ,[adj_iscontinue]
      ,[adj_starttime]
      ,[adj_stoptime]
      ,[adj_remark]
      ,[adj_date]
      ,[adj_renter]
      ,[adj_rentaddress]
      ,[adj_noid]
      ,[adj_reason]
      ,@YearMonth
      ,0
  FROM ReduceRecord where  [EffectDate]=@YearMonth-1 --上月
	and (cast([adj_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([adj_noid] as varchar(8))+cast([EffectDate] as varchar(4))) 
	from ReduceRecord where  [EffectDate]= @YearMonth  )--已存在不要


	--对应
	update ReduceRecord set applyid=
	(select top 1 apply_id from Apply where Apply.apply_noid=ReduceRecord.[adj_noid] and  Apply.[EffectDate]=ReduceRecord.[EffectDate])
	where applyid=0
	-----------------------------------------------------
	insert into SubsidyRecord
	SELECT       
	   [adj_longid]
      ,[adj_checked]
      ,[adj_nowmoney]
      ,0
      ,[adj_nowarea]
      ,[adj_iscontinue]
      ,[adj_starttime]
      ,[adj_stoptime]
      ,[adj_remark]
      ,[adj_date]
      ,[adj_noid]
      ,[adj_reason]
      ,@YearMonth
      ,0
  FROM SubsidyRecord where  [EffectDate]=@YearMonth-1 --上月
	and (cast([adj_noid] as varchar(8))+cast([EffectDate] as varchar(4))) not in 
	(select (cast([adj_noid] as varchar(8))+cast([EffectDate] as varchar(4))) 
	from SubsidyRecord where  [EffectDate]= @YearMonth  )--已存在不要


	--对应
	update SubsidyRecord set applyid=
	(select top 1 apply_id from Apply where Apply.apply_noid=SubsidyRecord.[adj_noid] and  Apply.[EffectDate]=SubsidyRecord.[EffectDate])
where applyid=0
	
End
--exec BuildDataByYearMonth 200909